/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import OBJECT.LichTrinh;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author TAN
 */
public class LichTrinhDAO {
    private Connect connect;


    public LichTrinhDAO(){}
    

     public LichTrinhDAO(String User ,String Pass,String port,String DataBase,String Host) {
        connect =new Connect( User , Pass, port, DataBase, Host);
     }
     
     public List<LichTrinh> LayLichTrinhTheoTuyenDuongVaTau(int MaTuyenDuong, int MaTau){
         List<LichTrinh> ds = new ArrayList<LichTrinh>();
         LichTrinh lt = null;
         Connection conn = null;
         CallableStatement cals = null;
         ResultSet rs = null;
         try {
//             conn = util.Connection.getConnection();
             conn = connect.GetConnect();
             cals = conn.prepareCall("{call sp_TimKiemLichTrinhTheoTauVaTuyenDuong(?,?)}");
             
             cals.setInt("MaTau", MaTau);
             cals.setInt("MaTuyenDuong", MaTuyenDuong);
             
             rs = cals.executeQuery();
             
             while(rs.next()){
                 
                 lt = new LichTrinh();
                 lt.setMaLichTrinh(rs.getInt(1));
                 lt.setMaTuyenDuong(rs.getInt(2));
                 lt.setMaTau(rs.getInt(3));
                 lt.setTGKhoiHanh(rs.getDate(4));
                 lt.setTGDen(rs.getDate(5));
                 lt.setContent(rs.getInt(6));
                 lt.setTinhTrang(rs.getString(7));
                 ds.add(lt);
             }
         } catch (Exception e) {
             System.err.println(e.getMessage());
         }finally{
             try {
                 if(rs != null){
                     rs.close();
                 }
                 cals.close();
                 CloseConnect();
             } catch (Exception e) {
             }
         }
         return ds;
     }
     
     public List<LichTrinh> TraCuuLichTrinh(String tuyenDuong, String tau, java.sql.Date thoiGianKhoiHanh, java.sql.Date thoiGianDen
                                                    , int soContainerConTrong, String tinhTrang){
         List<LichTrinh> ds = new ArrayList<LichTrinh>();
         CallableStatement cals = null;
         Connection conn = null;
         ResultSet rs = null;
         LichTrinh lt = null;
         String err = "";
         try {
//             conn = util.Connection.getConnection();
             conn = connect.GetConnect();
             cals = conn.prepareCall("{call sp_TraCuuLichTrinh(?,?,?,?,?,?,?)}");
             cals.setString("TenTuyenDuong", tuyenDuong);
             cals.setString("TenTau", tau);
             cals.setDate("ThoiGianKhoiHanh", thoiGianKhoiHanh);
             cals.setDate("ThoiGianDen", thoiGianDen);
             cals.setInt("SoContainerConTrong", soContainerConTrong);
             cals.setString("TinhTrang", tinhTrang);
             cals.registerOutParameter(7, Types.NVARCHAR);
             cals.setString("err", err);
             
             
             rs = cals.executeQuery();
//             err = cals.getString("err");
             
             if(err.equals("")){
                 
                 while(rs.next()){
                    lt = new LichTrinh();
                    lt.setMaLichTrinh(rs.getInt("Ma_LichTrinhVC"));
                    lt.setMaTau(rs.getInt("Ma_Tau"));
                    lt.setMaTuyenDuong(rs.getInt("Ma_TuyenDuong"));
                    lt.setContent(rs.getInt("SoContainerConTrong"));
                    lt.setTGDen(rs.getDate("ThoiGianDen"));
                    lt.setTGKhoiHanh(rs.getDate("ThoiGianKhoiHanh"));
                    lt.setTinhTrang(rs.getString("TinhTrang"));
                  //  lt.setTenTau(rs.getString("TenTau"));
                 //   lt.setTenTuyenDuong(rs.getString("Ten_TuyenDuong"));
                    ds.add(lt);
                }
                
             }else{
                 JOptionPane.showMessageDialog(null, err,"Thông báo",JOptionPane.ERROR_MESSAGE);
             }
             
             
         } catch (Exception e) {
             System.err.println(e.getMessage());
         }finally{
             try {
                 if(rs != null){
                     rs.close();
                 }
                 cals.close();
                 CloseConnect();
             } catch (Exception e) {
             }
         }
         
         return ds;
     }


     public CallableStatement DanhSachLichTrinh()
    {
        Connection conn=connect.GetConnect();
        ResultSet result=null;
        CallableStatement cals=null;
        try {
             cals=conn.prepareCall("{call sp_ThongTinLichTrinh(?)}");
              return cals;
        } catch (SQLException ex) {
            
        }
       return cals;
    }
     public LichTrinh Search1LichTrinh(int malt)
      {
          LichTrinh lt=new LichTrinh();
          Connection conn=connect.GetConnect();
          try
          {
              CallableStatement cals=conn.prepareCall("{call ThongTin1LichTrinh(?)}");
              cals.setInt(1, malt);
              ResultSet rs=cals.executeQuery();
              if(rs.next())
              {
              lt.setMaLichTrinh(rs.getInt("Ma_LichTrinhVC"));
              lt.setMaTau(rs.getInt("Ma_Tau"));
              lt.setMaTuyenDuong(rs.getInt("Ma_TuyenDuong"));
              lt.setTGKhoiHanh(rs.getTimestamp("ThoiGianKhoiHanh"));
              lt.setTGDen(rs.getTimestamp("ThoiGianDen"));
              lt.setContent(rs.getInt("SoContainerConTrong"));
             lt.setTinhTrang(rs.getString("TinhTrang"));
              }
              rs.close();
          }catch(Exception e)
          {
              return null;
          }
          finally{
              CloseConnect();
          }
          return lt;
      }
     public String ThemLichTrinh(LichTrinh lt)
    {   String Loi="";
        Connection conn=connect.GetConnect();
        try {
            CallableStatement cals=conn.prepareCall("{call sp_ThemLichTrinh(?,?,?,?,?)}");
                cals.setInt(1, lt.getMaTuyenDuong());
                cals.setInt(2, lt.getMaTau());                
                cals.setTimestamp(3,new java.sql.Timestamp(lt.getTGKhoiHanh().getTime()));                 
                cals.setTimestamp(4,new java.sql.Timestamp(lt.getTGDen().getTime()));
                //System.out.println("sao the");
               cals.registerOutParameter(5, java.sql.Types.NVARCHAR);
               cals.execute();
               Loi=cals.getString(5);
                cals.close();  
        } catch (SQLException ex) {
            Loi="Falied";
            
        }
       finally
        {
            CloseConnect();
        }
        return Loi;
    }
    public String SuaLichTRinh(LichTrinh lt)
    {   String Loi="";
        Connection conn=connect.GetConnect();
        try {
            CallableStatement cals=conn.prepareCall("{call sp_SuaLichTrinh(?,?,?,?,?,?,?,?)}");
                cals.setInt(1, lt.getMaLichTrinh());
                cals.setInt(2, lt.getMaTuyenDuong());
                cals.setInt(3, lt.getMaTau());
                cals.setDate(4,new java.sql.Date(lt.getTGKhoiHanh().getTime()));
                cals.setDate(5,new java.sql.Date(lt.getTGDen().getTime()) );
                cals.setInt(6, lt.getContent());
                cals.setString(7, lt.getTinhTrang());
               cals.registerOutParameter(8, java.sql.Types.NVARCHAR);
               cals.execute();
               Loi=cals.getString(8);
                 cals.close();
        } catch (SQLException ex) {
            Loi="Falied";
            
        }
       finally
        {   
            CloseConnect();
        }
        return Loi;
    }
    public String XoaLichTrinh(int malt)
    {
        String Message="";
        Connection conn=connect.GetConnect();
        try {
            CallableStatement cals=conn.prepareCall("{call sp_XoaLichTrinh(?,?)}");
            cals.setInt(1, malt);
            cals.setString(2, "");
            cals.registerOutParameter(2, java.sql.Types.NVARCHAR);
            
            cals.execute();
               Message=cals.getString(2);
                 
               
               
        } catch (SQLException ex) {
          Message="Faild";
            System.out.println(ex.getMessage());
        }
        finally{
            CloseConnect();
        }
       return Message;
        
    }
    public CallableStatement TimLichTrinhTheoTD(int matd)
     {
                        Connection conn=connect.GetConnect();
                        ResultSet result=null;
                        CallableStatement cals=null;
                        try {
                                cals=conn.prepareCall("{call sp_TimKiemLichTrinhTheoTD(?)}");
                                cals.setInt(1, matd);
                            return cals;
                        } catch (SQLException ex) {

                        }
                    return cals;                    
              }
      public void CloseConnect()
      {
          try
          {
          connect.Close();
          }catch(Exception e)
          {
              ;
          }
      } 
}
